Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator level post which will function as a brief overview of Excel’s Data Types feature. The basic Data Types (Geography and Stocks) are available in Excel 365, and if you’re on the Insider program or a faster update pathway you might have access to more.
What are data types?
Data types can be added to an Excel Table to mark a particular column as being a certain type of data. Excel will then do its best to identify each item in that column and associate it with an online database of information about items of that type.
Let’s take an example of some geographic data – the names of some cities. We type out our names and then try the Geography button to convert these to the City data type. Most are recognised automatically:
The “?” names are ambiguous or otherwise not recognised by Excel. By highlighting each, we are able to select which city we mean from a sidebar:
Once this is done, we have now set up our linked data type.
What do data types let you do?
Once a field is recognised as a linked data type, we can add new columns to our Excel Table automatically by pressing the special Add Column button that appears when we highlight any cell in the column:
We can then pick from a very large selection of preset information about that data type provided by Microsoft:
And we then see the resulting data added to the Table:
Like everything with Excel Tables, this connection is live and automatically updates, so we can add more cities on the bottom of the Table and Excel will automatically identify them and fill in any connected fields. This whole thing makes for an easily updated and flexible way of filling in live data on a variety of common fields. For example, you can get information about stocks and exchange rates using the Stocks or Currency types:
If your Excel version has it, you can also use the related STOCKHISTORY function to get the historic information about a particular stock or currency pair:
Try downloading the example file (if you have Office 365) and exploring for yourself!
You may also like
Excel community
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.
- Excel Tips and Tricks #496 – ‘Check Performance’ in Excel
- Excel Tips and Tricks #495 - Excel “Tick”ery!
- Excel Tips & Tricks #494 - How to add a custom ribbon to your workbook part 3
- Excel Tips & Tricks #493 - How to add a custom ribbon to your workbook part 2
- Excel Tips & Tricks #492 - How to add a custom ribbon to your workbook
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
